<?php 
require_once dirname(__FILE__)."/../config/dbconn.php"; 
 
class Comments extends Dbconn {
    //метод принимает id юзера, id поста, текст коммента
    //и записывает в БД
    function add_comment($arr){
        $sql = Comments::getConnect()->prepare("
                 INSERT INTO comments(
                                      name_user,
                                      id_post,
                                      message,
                                      date_create)
                 VALUES(:name_user,
                        :id_post,
                        :message,
                        :date)
                 ");         
        $sql->execute($arr);
    }	
    
    //метод делает выборку всех постов с указаным id поста
    function show_comments($id_post){
        $sql = Comments::getConnect()->prepare("
                 SELECT id,
                        name_user,
                        message,
                        date_create
                FROM comments
                WHERE id_post = :id_post AND
                visible = 1
                ORDER BY id ASC
                ");
        $sql->bindParam(':id_post', intval($id_post));
        $sql->execute();        
        return $sql;
    }
    
    //удаление выбранного комментария
    function delete_select_comment($id){
        $sql = Comments::getConnect()->prepare("
                 DELETE FROM comments
                 WHERE id = :id
                 ");
        
        $sql->bindParam(':id', intval($id));
        $sql->execute();
    }
    
    //выборка пяти последних комментариев
    function get_last_comments(){
        $sql = Comments::getConnect()->query("
                 SELECT name_user,
                        id_post,
                        message
                 FROM comments
                 WHERE visible = 1
                 ORDER BY date_create DESC
                 LIMIT 0, 5
                 ");
        
        return $sql;
    }
    
    function show_all_comments(){
        $sql = Comments::getConnect()->query("
                 SELECT c.`id`,
                        c.`id_post`,
                        c.`name_user`,
                        c.`message`, 
                        c.`visible`, 
                        c.`date_create`, 
                        p.`name`
                 FROM comments AS c, 
                      posts AS p
                 WHERE c.`id_post` = p.`id`
                 ORDER BY id DESC
                 ");
        
        return $sql;
    }
    
    function show_visible_comments(){
        $sql = Comments::getConnect()->query("
                 SELECT c.`id`,
                        c.`id_post`, 
                        c.`name_user`, 
                        c.`message`, 
                        c.`visible`, 
                        c.`date_create`, 
                        p.`name`
                 FROM comments AS c, 
                      posts AS p
                 WHERE c.`id_post` = p.`id` AND 
                       c.`visible` = 1
                 ");
        
        return $sql;
    }
    
    function show_invisible_comments(){
        $sql = Comments::getConnect()->query("
                 SELECT c.`id`,
                        c.`id_post`, 
                        c.`name_user`, 
                        c.`message`, 
                        c.`visible`, 
                        c.`date_create`, 
                        p.`name`
                 FROM comments AS c, 
                      posts AS p
                 WHERE c.`id_post` = p.`id` AND 
                       c.`visible` = 0
                 ORDER by id DESC
                 ");
        
        return $sql;
    }
    
    function update_comment($id, $message){
        $sql = Comments::getConnect()->prepare("
                 UPDATE comments
                 SET message = :message
                 WHERE id = :id
                 ");
        $sql->bindParam(':message', $message);
        $sql->bindParam('id', intval($id));
        $sql->execute();         
    }
    function posts_count($id){
        $sql = Comments::getConnect()->prepare("
                 SELECT id 
                 FROM posts
                 WHERE id = :id
                 ");
       $sql->bindParam(':id', intval($id), PDO::PARAM_INT);
       $sql->execute();
       return $sql;
    }
    function delete_comment($id){
        $sql = Comments::getConnect()->prepare("
                 DELETE FROM comments
                 WHERE id = :id
                 ");
        $sql->bindParam(':id', intval($id));
        $sql->execute();
    }
    function make_visible($id){
        $sql = Comments::getConnect()->prepare("
                 UPDATE comments
                 SET visible = 1
                 WHERE id = :id
                 ");
        $sql->bindParam(':id', intval($id));
        $sql->execute();         
    }
    function make_invisible($id){
        $sql = Comments::getConnect()->prepare("
                 UPDATE comments
                 SET visible = 0
                 WHERE id = :id
                 ");
        $sql->bindParam(':id', intval($id));
        $sql->execute();
    }
    function show_select_comment($id){
        $sql = Comments::getConnect()->prepare("
                 SELECT name_user,
                        message,
                        date_create
                 FROM comments
                 WHERE id = :id
                 ");
        
        $sql->bindParam(':id', intval($id));
        $sql->execute();
        return $sql;
    }                
}
